package com.hnevc.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hnevc.domain.Course;
import java.sql.PreparedStatement;



public class CourseDao implements ICourseDao {
	// 查询
	public List<Course> getData(){
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		ArrayList<Course> dataList=null;
		// 由于数据存放在数据库中，因此，我们需要通过jdbc来读取数据
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=UTF-8","root","root");
			stmt = con.createStatement();
			rs = stmt.executeQuery("select courseid,coursename,coursedegree,courseaddress from course");
			dataList = new ArrayList<Course>();
			while(rs.next()){//rs遍历结果集，将结果集中的数据保存到dataList中
				Course course = new Course(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getString(4));
				dataList.add(course);//将一个课程信息加入课程数据链表
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return dataList;
	}
	// 修改
	public void updateData(Course course) {
		try {
			//加载驱动程序到类中
			Class.forName("com.jdbc.mysql.Driver");
			// 获取连接对象
			String url="jdbc:mysql://127.0.0.1:3306/jdbc";
			String user="root";
			String password="root";
			Connection con = DriverManager.getConnection(url, user, password);
			// 在连接对象上，创建一个SQL命令的承载器
			Statement stmt = con.createStatement();
			// 执行SQL命令
			String sql = "update course "
					+ "set coursename='"+course.getCoursename()
					+"',coursedegree= "+course.getCoursedegreee()
					+",courseaddress='"+course.getCourseaddress()+"'"
					+ " where courseid= "+course.getCourseid()+"";
			System.out.println(sql);
			int result = stmt.executeUpdate(sql);
			// 处理结果
			if(result>0) {
				System.out.println("修改成功");
			}
			
			// 关闭
			stmt.close();
			con.close();
		}catch(Exception e) {
			System.out.println(e.getMessage());
		}
		
	} 
	// 添加
	public void insertData(Course course) {
		Connection con=null;
		PreparedStatement stmt=null;


		// 由于数据存放在数据库中，因此，我们需要通过jdbc来读取数据
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8","root","root");
			stmt = con.prepareStatement("insert into course(coursename,coursedegree,courseaddress) "
					+ "values(?,?,?)");
			stmt.setString(1, course.getCoursename());
			stmt.setString(2, course.getCoursedegreee());
			stmt.setString(3, course.getCoursedegreee());
			int result = stmt.executeUpdate();
			if(result ==1) {
				System.out.println("添加成功");
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			if(stmt!=null){
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
	// 删除
	public void deleteData(int courseId) {
	
		Connection con=null;
		Statement stmt=null;
		
		try {
			//加载驱动程序到类中
			Class.forName("com.jdbc.mysql.Driver");
			// 获取连接对象
			String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=UTF-8";
			String user = "root";
			String password = "root";
			con = DriverManager.getConnection(url,user,password);
			// 在连接对象上，创建一个SQL命令的承载器
			stmt = con.createStatement();
			String sql ="delete from course where courseid="+courseId;
			// 执行SQL命令
			int result = stmt.executeUpdate(sql);//result 受影响的记录条数
			// 处理结果
			if(result == 1) {
				System.out.println("删除成功");
			}
			// 关闭
			stmt.close();
			con.close();
		}catch(Exception e) {
			
		}
		
		
	}
}
